'''
安装插件
pip install mysql_connector

执行数据库脚本
python_mysql.sql
'''

from mysql import connector

# 建立连接
conn = connector.connect(
    host='127.0.0.1',  # 连接底座
    user='root',  # 数据库用户名
    password='root',  # 用户名对应的密码
    database='python_mysql',  #
)
# 创建一个游标对象
cursor = conn.cursor()


# 创建表
def create_table():
    sql = "create table user(" \
          "id int primary key auto_increment," \
          "name varchar(255)," \
          "sex int default 0 comment '性别（0:男）'," \
          "age int default 0," \
          "phone int default 0," \
          "address varchar(255)," \
          "email varchar(255)" \
          ")";
    cursor.execute(sql)


# 插入数据
def insert_table():
    # 循环插入数据
    for item in range(10):
        sql = "insert into user(`name`,sex,age,phone,address,email) values(%s,%s,%s,%s,%s,%s)"
        cursor.execute(sql, ("用户-" + str(item), 0, 20 + item, "130888866" + str(item), '北京市朝阳区某个犄角旮旯',
                             "130888866" + str(item) + "@163.com"))

    conn.commit()  # 数据表内容有更新，必须使用到该语句
    print("插入最后一条记录, ID:", cursor.lastrowid)


# 更新语句
def update_table(update_id):
    sql = "update user set sex=1 where id=" + update_id
    cursor.execute(sql)

    conn.commit()  # 数据操作需要使用
    print("1 条记录已更新, ID:", update_id)


# 查询
def select_table():
    # 以倒叙的形式显示，并取出10条数据
    cursor.execute("select * from user order by id desc limit 10")
    # 获取所有的数据
    # results = cursor.fetchall()
    # 获取一条数据
    results = cursor.fetchone()
    # 循环打印数据
    for row in results:
        print(row)


# ================================测试开始
# create_table()
# insert_table()
# update_table("1")
select_table()
